
const express = require("express");
const router = express.Router();
const utils = require("../../utils/index.js");
const pools = require("../../utils/pools.js");
const xlsx = require("node-xlsx");
let fs = require("fs");

//项目管理
//查询
router.post("/getProjectList", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT  id,cost_number as costNumber,project_name as projectName,remarks,state,created_time as createdTime,created_user as createdUser,updated_time as updatedTime,updated_user as  updatedUser FROM projects where  state=0 `;
    sql=utils.setTwoLike(sql,"cost_number","project_name",obj.title);
    // let {total}=await utils.getSum({sql,name:"projects",res,req});
    sql+=` ORDER BY updated_time DESC`;
    // sql=utils.pageSize(sql,obj.pageNo,obj.pageSize);
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result}));
});
router.post("/getProjectById", async (req, res) => {
	let obj=req.body;
	let sql = `
	SELECT
	a.id,
	a.cost_number AS costNumber,
	a.project_name AS projectName,
	a.device_type AS deviceType,
	a.year,
	a.drive_box_type,
	a.devices_number,
	a.fixtures_channels,
	a.project_manager,
	a.software_main_manager,
	a.software_manager,
	a.charge_discharge_manager,
	a.visual_director,
	a.factory_situation,
	a.customer_situation,
	a.factory_fat_time,
	a.delivery_time,
	a.customer_fat_time,
	a.remarks,
	a.state,
	a.created_time AS createdTime,
	a.created_user AS createdUser,
	a.updated_time AS updatedTime,
	a.updated_user AS updatedUser,
	b.number
FROM
	projects a
	LEFT JOIN (
		SELECT
			cost_number,
			COUNT(*) AS number
		FROM
			production_metadata
		GROUP BY
			cost_number
	) b ON a.cost_number = b.cost_number
WHERE
	 a.state=0  and a.id=?`;
	let {result}=await pools({sql,val:[obj.id],res,req});
	res.send(utils.returnData({ data: result[0]}));
});
router.post("/getProjectGroup", async (req, res) => {
    let sql = `SELECT  id,cost_number as costNumber,CONCAT(cost_number, '/', project_name) AS projectName FROM projects where  state=0 `;
    sql+=` ORDER BY updated_time DESC`;
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result}));
});
//新增
router.post("/insertProject", async (req, res) => {
    let obj=req.body;
    let sql = `INSERT INTO  projects (cost_number,project_name,remarks,state,created_time,created_user, updated_time,updated_user) VALUES (?,?,?,0,?,?,?,?)`;
    await pools({sql,val:[obj.costNumber,obj.projectName,obj.remarks,new Date(),0,new Date(),0],res,req,run:false});
});
//删除
router.post("/deleteProject", async (req, res) => {
    let obj=req.body;
    let sql = `update projects set state=2  WHERE id = ?;`;
    await pools({sql,val:[obj.id],res,req,run:false});
});
//编辑
router.post("/updateProject", async (req, res) => {
    let obj=req.body;
    let sql = `update projects set cost_number=?,project_name=? ,remarks=?,updated_time=?,updated_user=? WHERE id = ?; `;
    await pools({sql,val:[obj.costNumber,obj.projectName,obj.remarks,new Date(),0,obj.id],res,req,run:false});
});

router.get("/test",async (req,res)=>{
    res.send(utils.returnData({data:'order133'}));
})



//项目产量分类统计
router.post("/getProjectProduct", async (req, res) => {
	let obj=req.body;
	let sql = `select * from project_statistics	
	WHERE
	cost_number = '${obj.costNumber}'`;
	let {result}=await pools({sql,val:[],res,req});
	res.send(utils.returnData({data: result}));
  });

//项目纵览数据

router.post("/getProjectTotalList", async (req, res) => {
    let obj=req.body;
	let sql ='select h.* from project_statistics  h';
     if(obj.title){
        sql+=` where  h.cost_number LIKE "%${obj.title}%" or h.project_name LIKE "%${obj.title}%"`
     }
	 sql +=` order by h.project_created_time  desc`;
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result }));
});

module.exports = router;
